USE <database_name>;
GO

-- index scans percentage should be high, index updates percentage should be low when compressed
SELECT
	OBJECT_NAME(i.object_id) AS table_name,
	i.name AS index_name,
    ios.partition_number AS 'partition_id',
    ios.index_id AS index_id,
	i.type_desc AS index_type,
	CAST(ios.range_scan_count * 100.0 /
           (ios.range_scan_count + ios.leaf_insert_count
            + ios.leaf_delete_count + ios.leaf_update_count
            + ios.leaf_page_merge_count + ios.singleton_lookup_count
           ) AS DECIMAL(18, 2)) AS scans_percent,
    CAST(ios.leaf_update_count * 100.0 /
           (ios.range_scan_count + ios.leaf_insert_count
            + ios.leaf_delete_count + ios.leaf_update_count
            + ios.leaf_page_merge_count + ios.singleton_lookup_count
           ) AS DECIMAL(18, 2)) AS updates_percent
FROM sys.indexes i WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) ios
	ON i.object_id = ios.object_id AND i.index_id = i.index_id
WHERE (ios.range_scan_count + ios.leaf_insert_count
       + ios.leaf_delete_count + leaf_update_count
       + ios.leaf_page_merge_count + ios.singleton_lookup_count) != 0
AND OBJECTPROPERTY(ios.object_id,'IsUserTable') = 1
ORDER BY scans_percent DESC OPTION (RECOMPILE); -- updates_percent ASC;
GO

-- calculate savings with PAGE data compression
EXECUTE sys.sp_estimate_data_compression_savings '<schema_name>', '<table_name>', NULL, NULL, 'PAGE';
GO

-- PAGE|ROW compress an index on a (big) table
ALTER INDEX <index_name> 
ON <schema_name>.<table_name> REBUILD PARTITION = ALL 
WITH (ONLINE = ON, MAXDOP = 4, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE|ROW);

-- verify the PAGE compression success rate
SELECT
    DISTINCT OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    p.partition_number AS partition,
    ios.page_compression_attempt_count,
    ios.page_compression_success_count,
    ios.page_compression_success_count * 1.0 / ios.page_compression_attempt_count
        AS success_rate
FROM sys.indexes AS i
INNER JOIN sys.partitions AS p
	ON p.object_id = i.object_id
CROSS APPLY
    sys.dm_db_index_operational_stats (
        DB_ID(), i.object_id, i.index_id, p.partition_number) AS ios
WHERE
    p.data_compression = 2 --0 = NONE;1 = ROW;2 = PAGE;3 = COLUMNSTORE: for SQL Server >= 2012;4 = COLUMNSTORE_ARCHIVE : for SQL Server >= 2014
    AND ios.page_compression_attempt_count > 0
ORDER BY success_rate;